using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VSCALEPG
    {
        public DataRow Convert2SCALEPG(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QuerySCALEPG(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["SCPGMANDANT"] = " ".PadRight(2 ); 
         dwReturn["SCPGJJJJ"] = " ".PadRight(4 ); 
         dwReturn["SCPGPLGRP"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG11"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG12"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG13"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG14"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG21"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG22"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG23"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG24"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG31"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG32"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG33"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG34"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG41"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG42"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG43"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG44"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG51"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG52"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG53"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG54"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG61"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG62"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG63"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG64"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG71"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG72"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG73"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG74"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG81"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG82"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG83"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG84"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG91"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG92"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG93"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG94"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG101"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG102"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG103"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG104"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG111"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG112"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG113"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG114"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG121"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG122"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG123"] = " ".PadRight(2 ); 
         dwReturn["SCPGTAG124"] = " ".PadRight(2 ); 
         dwReturn["SCPGAENZL"] = " ".PadRight(0 ); 
         dwReturn["SCPGAENB"] = " ".PadRight(8 ); 
         dwReturn["SCPGAEND"] = " ".PadRight(0 ); 
         dwReturn["SCPGNEUB"] = " ".PadRight(8 ); 
         dwReturn["SCPGNEUD"] = " ".PadRight(0 ); 
         dwReturn["SCPGFILLER"] = " ".PadRight(70 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapSCALEPG(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveSCALEPG(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QuerySCALEPG(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapSCALEPG(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertSCALEPG(dwLocal, ITXDBVERSION);
            }
            UpdateSCALEPG(dwLocal);
        }
        public void InsertSCALEPG(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO SCALEPG(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QuerySCALEPG(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM SCALEPG WHERE  ROWNUM<2 ";
                    break;
              
                case 99:
                    or1.CommandText = "SELECT * FROM SCALEPG  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "SCALEPG";
            Return = Result;
            return Return;
        }
        public void DeleteSCALEPG(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM SCALEPG   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateSCALEPG(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE SCALEPG SET "
                    +"SCPGMANDANT= :SCPGMANDANT,"
                    +"SCPGJJJJ= :SCPGJJJJ,"
                    +"SCPGPLGRP= :SCPGPLGRP,"
                    +"SCPGTAG11= :SCPGTAG11,"
                    +"SCPGTAG12= :SCPGTAG12,"
                    +"SCPGTAG13= :SCPGTAG13,"
                    +"SCPGTAG14= :SCPGTAG14,"
                    +"SCPGTAG21= :SCPGTAG21,"
                    +"SCPGTAG22= :SCPGTAG22,"
                    +"SCPGTAG23= :SCPGTAG23,"
                    +"SCPGTAG24= :SCPGTAG24,"
                    +"SCPGTAG31= :SCPGTAG31,"
                    +"SCPGTAG32= :SCPGTAG32,"
                    +"SCPGTAG33= :SCPGTAG33,"
                    +"SCPGTAG34= :SCPGTAG34,"
                    +"SCPGTAG41= :SCPGTAG41,"
                    +"SCPGTAG42= :SCPGTAG42,"
                    +"SCPGTAG43= :SCPGTAG43,"
                    +"SCPGTAG44= :SCPGTAG44,"
                    +"SCPGTAG51= :SCPGTAG51,"
                    +"SCPGTAG52= :SCPGTAG52,"
                    +"SCPGTAG53= :SCPGTAG53,"
                    +"SCPGTAG54= :SCPGTAG54,"
                    +"SCPGTAG61= :SCPGTAG61,"
                    +"SCPGTAG62= :SCPGTAG62,"
                    +"SCPGTAG63= :SCPGTAG63,"
                    +"SCPGTAG64= :SCPGTAG64,"
                    +"SCPGTAG71= :SCPGTAG71,"
                    +"SCPGTAG72= :SCPGTAG72,"
                    +"SCPGTAG73= :SCPGTAG73,"
                    +"SCPGTAG74= :SCPGTAG74,"
                    +"SCPGTAG81= :SCPGTAG81,"
                    +"SCPGTAG82= :SCPGTAG82,"
                    +"SCPGTAG83= :SCPGTAG83,"
                    +"SCPGTAG84= :SCPGTAG84,"
                    +"SCPGTAG91= :SCPGTAG91,"
                    +"SCPGTAG92= :SCPGTAG92,"
                    +"SCPGTAG93= :SCPGTAG93,"
                    +"SCPGTAG94= :SCPGTAG94,"
                    +"SCPGTAG101= :SCPGTAG101,"
                    +"SCPGTAG102= :SCPGTAG102,"
                    +"SCPGTAG103= :SCPGTAG103,"
                    +"SCPGTAG104= :SCPGTAG104,"
                    +"SCPGTAG111= :SCPGTAG111,"
                    +"SCPGTAG112= :SCPGTAG112,"
                    +"SCPGTAG113= :SCPGTAG113,"
                    +"SCPGTAG114= :SCPGTAG114,"
                    +"SCPGTAG121= :SCPGTAG121,"
                    +"SCPGTAG122= :SCPGTAG122,"
                    +"SCPGTAG123= :SCPGTAG123,"
                    +"SCPGTAG124= :SCPGTAG124,"
                    +"SCPGAENZL= :SCPGAENZL,"
                    +"SCPGAENB= :SCPGAENB,"
                    +"SCPGAEND= :SCPGAEND,"
                    +"SCPGNEUB= :SCPGNEUB,"
                    +"SCPGNEUD= :SCPGNEUD,"
                    +"SCPGFILLER= :SCPGFILLER,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("SCPGMANDANT", OracleType.NVarChar).Value = dwData["SCPGMANDANT"].ToString(); 
          or1.Parameters.Add("SCPGJJJJ", OracleType.NVarChar).Value = dwData["SCPGJJJJ"].ToString(); 
          or1.Parameters.Add("SCPGPLGRP", OracleType.NVarChar).Value = dwData["SCPGPLGRP"].ToString(); 
          or1.Parameters.Add("SCPGTAG11", OracleType.NVarChar).Value = dwData["SCPGTAG11"].ToString(); 
          or1.Parameters.Add("SCPGTAG12", OracleType.NVarChar).Value = dwData["SCPGTAG12"].ToString(); 
          or1.Parameters.Add("SCPGTAG13", OracleType.NVarChar).Value = dwData["SCPGTAG13"].ToString(); 
          or1.Parameters.Add("SCPGTAG14", OracleType.NVarChar).Value = dwData["SCPGTAG14"].ToString(); 
          or1.Parameters.Add("SCPGTAG21", OracleType.NVarChar).Value = dwData["SCPGTAG21"].ToString(); 
          or1.Parameters.Add("SCPGTAG22", OracleType.NVarChar).Value = dwData["SCPGTAG22"].ToString(); 
          or1.Parameters.Add("SCPGTAG23", OracleType.NVarChar).Value = dwData["SCPGTAG23"].ToString(); 
          or1.Parameters.Add("SCPGTAG24", OracleType.NVarChar).Value = dwData["SCPGTAG24"].ToString(); 
          or1.Parameters.Add("SCPGTAG31", OracleType.NVarChar).Value = dwData["SCPGTAG31"].ToString(); 
          or1.Parameters.Add("SCPGTAG32", OracleType.NVarChar).Value = dwData["SCPGTAG32"].ToString(); 
          or1.Parameters.Add("SCPGTAG33", OracleType.NVarChar).Value = dwData["SCPGTAG33"].ToString(); 
          or1.Parameters.Add("SCPGTAG34", OracleType.NVarChar).Value = dwData["SCPGTAG34"].ToString(); 
          or1.Parameters.Add("SCPGTAG41", OracleType.NVarChar).Value = dwData["SCPGTAG41"].ToString(); 
          or1.Parameters.Add("SCPGTAG42", OracleType.NVarChar).Value = dwData["SCPGTAG42"].ToString(); 
          or1.Parameters.Add("SCPGTAG43", OracleType.NVarChar).Value = dwData["SCPGTAG43"].ToString(); 
          or1.Parameters.Add("SCPGTAG44", OracleType.NVarChar).Value = dwData["SCPGTAG44"].ToString(); 
          or1.Parameters.Add("SCPGTAG51", OracleType.NVarChar).Value = dwData["SCPGTAG51"].ToString(); 
          or1.Parameters.Add("SCPGTAG52", OracleType.NVarChar).Value = dwData["SCPGTAG52"].ToString(); 
          or1.Parameters.Add("SCPGTAG53", OracleType.NVarChar).Value = dwData["SCPGTAG53"].ToString(); 
          or1.Parameters.Add("SCPGTAG54", OracleType.NVarChar).Value = dwData["SCPGTAG54"].ToString(); 
          or1.Parameters.Add("SCPGTAG61", OracleType.NVarChar).Value = dwData["SCPGTAG61"].ToString(); 
          or1.Parameters.Add("SCPGTAG62", OracleType.NVarChar).Value = dwData["SCPGTAG62"].ToString(); 
          or1.Parameters.Add("SCPGTAG63", OracleType.NVarChar).Value = dwData["SCPGTAG63"].ToString(); 
          or1.Parameters.Add("SCPGTAG64", OracleType.NVarChar).Value = dwData["SCPGTAG64"].ToString(); 
          or1.Parameters.Add("SCPGTAG71", OracleType.NVarChar).Value = dwData["SCPGTAG71"].ToString(); 
          or1.Parameters.Add("SCPGTAG72", OracleType.NVarChar).Value = dwData["SCPGTAG72"].ToString(); 
          or1.Parameters.Add("SCPGTAG73", OracleType.NVarChar).Value = dwData["SCPGTAG73"].ToString(); 
          or1.Parameters.Add("SCPGTAG74", OracleType.NVarChar).Value = dwData["SCPGTAG74"].ToString(); 
          or1.Parameters.Add("SCPGTAG81", OracleType.NVarChar).Value = dwData["SCPGTAG81"].ToString(); 
          or1.Parameters.Add("SCPGTAG82", OracleType.NVarChar).Value = dwData["SCPGTAG82"].ToString(); 
          or1.Parameters.Add("SCPGTAG83", OracleType.NVarChar).Value = dwData["SCPGTAG83"].ToString(); 
          or1.Parameters.Add("SCPGTAG84", OracleType.NVarChar).Value = dwData["SCPGTAG84"].ToString(); 
          or1.Parameters.Add("SCPGTAG91", OracleType.NVarChar).Value = dwData["SCPGTAG91"].ToString(); 
          or1.Parameters.Add("SCPGTAG92", OracleType.NVarChar).Value = dwData["SCPGTAG92"].ToString(); 
          or1.Parameters.Add("SCPGTAG93", OracleType.NVarChar).Value = dwData["SCPGTAG93"].ToString(); 
          or1.Parameters.Add("SCPGTAG94", OracleType.NVarChar).Value = dwData["SCPGTAG94"].ToString(); 
          or1.Parameters.Add("SCPGTAG101", OracleType.NVarChar).Value = dwData["SCPGTAG101"].ToString(); 
          or1.Parameters.Add("SCPGTAG102", OracleType.NVarChar).Value = dwData["SCPGTAG102"].ToString(); 
          or1.Parameters.Add("SCPGTAG103", OracleType.NVarChar).Value = dwData["SCPGTAG103"].ToString(); 
          or1.Parameters.Add("SCPGTAG104", OracleType.NVarChar).Value = dwData["SCPGTAG104"].ToString(); 
          or1.Parameters.Add("SCPGTAG111", OracleType.NVarChar).Value = dwData["SCPGTAG111"].ToString(); 
          or1.Parameters.Add("SCPGTAG112", OracleType.NVarChar).Value = dwData["SCPGTAG112"].ToString(); 
          or1.Parameters.Add("SCPGTAG113", OracleType.NVarChar).Value = dwData["SCPGTAG113"].ToString(); 
          or1.Parameters.Add("SCPGTAG114", OracleType.NVarChar).Value = dwData["SCPGTAG114"].ToString(); 
          or1.Parameters.Add("SCPGTAG121", OracleType.NVarChar).Value = dwData["SCPGTAG121"].ToString(); 
          or1.Parameters.Add("SCPGTAG122", OracleType.NVarChar).Value = dwData["SCPGTAG122"].ToString(); 
          or1.Parameters.Add("SCPGTAG123", OracleType.NVarChar).Value = dwData["SCPGTAG123"].ToString(); 
          or1.Parameters.Add("SCPGTAG124", OracleType.NVarChar).Value = dwData["SCPGTAG124"].ToString(); 
          or1.Parameters.Add("SCPGAENZL", OracleType.NVarChar).Value = dwData["SCPGAENZL"].ToString(); 
          or1.Parameters.Add("SCPGAENB", OracleType.NVarChar).Value = dwData["SCPGAENB"].ToString(); 
          or1.Parameters.Add("SCPGAEND", OracleType.NVarChar).Value = dwData["SCPGAEND"].ToString(); 
          or1.Parameters.Add("SCPGNEUB", OracleType.NVarChar).Value = dwData["SCPGNEUB"].ToString(); 
          or1.Parameters.Add("SCPGNEUD", OracleType.NVarChar).Value = dwData["SCPGNEUD"].ToString(); 
          or1.Parameters.Add("SCPGFILLER", OracleType.NVarChar).Value = dwData["SCPGFILLER"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
